package com.hongdee.scm.initStock.service.impl;

import com.hongdee.common.dao.IHibernateDao;
import com.hongdee.common.dao.IImpHibernateDao;
import com.hongdee.common.dao.IImpJdbcDao;
import com.hongdee.common.dao.IJdbcDao;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.scm.car.service.ITScmCarService;
import com.hongdee.scm.common.service.impl.Common;
import com.hongdee.scm.initStock.dao.ITScmInitStockDao;
import com.hongdee.scm.initStock.entity.TScmCarEx;
import com.hongdee.scm.initStock.entity.TScmEnterEx;
import com.hongdee.scm.initStock.entity.TScmStockEx;
import com.hongdee.scm.initStock.service.ITScmInitStockService;
import com.hongdee.scm.pushMessage.entity.TScmMapToJSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;

/**
 * Created by Administrator on 2017/9/22.
 */
@Service
public class TScmInitStockServiceImpl implements ITScmInitStockService {

    @Autowired
    private ITScmInitStockDao itScmInitStockDao;
    @Autowired
    private IImpJdbcDao impJdbcDao;
    @Autowired
    private IImpHibernateDao iImpHibernateDao;
    @Autowired
    private IJdbcDao iJdbcDao;
    @Autowired
    private IHibernateDao iHibernateDao;
    @Autowired
    private ITScmCarService tScmCarService;

    /**
     * 初次导入
     * @param uid
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map importIn(String uid, String start, String limit) throws Exception{

        String cid = TokenUtil.getCompanyId(uid);
        //导入到正式数据库
        this.impCar(cid);
        this.impStock(cid);
        this.impEnter(cid);

        //从t_scm_car_ex中删除t_scm_stock_ex没有的数据
        String sqls ="delete from t_scm_car_ex" +
                " where company_id =? and RIGHT(trim(vin), 8) not in" +
                " (select RIGHT(trim(s.vin), 8) from t_scm_stock_ex s" +
                " where s.company_id = ?)";
        this.itScmInitStockDao.executeSQL(sqls,cid,cid);

        //在正式数据库t_scm_stock,t_scm_car,t_scm_invoice_price,t_scm_input_price,t_scm_input_price_detail插入数据
        String sql = "select func_initStockFirst(?) ";
        List<String> params = new ArrayList<String>();
        if(!StringUtils.isEmpty(cid)){
            params.add(cid);
        }
        this.itScmInitStockDao.list(sql,params.toArray());
        return this.ifmatch(cid,start,limit);
    }

    /**
     * 第二次初始化
     * @param uid
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map importSecond(String uid,String start,String limit) throws Exception{

        String cid = TokenUtil.getCompanyId(uid);
        //从t_scm_car_ex 中删除 vin一样的数据
        String sqls ="delete from t_scm_car_ex" +
                " where vin in (select vin from t_scm_car where company_id = ?)";
        this.itScmInitStockDao.executeSQL(sqls,cid);

        String sql = "select func_initStockSecond(?) ";
        List<String> params = new ArrayList<String>();
        if(!StringUtils.isEmpty(cid)){
            params.add(cid);
        }
        this.itScmInitStockDao.list(sql,params.toArray());
        return this.ifmatch(cid,start,limit);
    }

    /**
     * 导入车辆数据
     */
    private static final String sqlCheckCarExist = "select id, vin from t_scm_car_ex where vin = ? and company_id = ? limit 1";
    private static  final DecimalFormat decimalFormat = new DecimalFormat("#.00");
    private void impCar(String cid) throws Exception{

        this.itScmInitStockDao.executeSQL("delete from t_scm_car_ex where company_id=?",cid);
        //编写查询源数据 HQL
        String hql = "from TScmCarEx where companyId = :companyId";
        //设置参数
        Map<String, Object> params = new HashMap();
        params.put("companyId", cid);
        //查询公司下的全部车辆
        List<TScmCarEx> tScmCarEx = iImpHibernateDao.findEntityListByHqlWithParams(hql, params);
        if (tScmCarEx == null) {
            throw new Exception("无效的数据！");
        }
        List<Object> idList = new ArrayList();
        String tempId;
        for (TScmCarEx car : tScmCarEx) {
            //源数据主键
            tempId = car.getId();
            //源数据主键
            idList.add(tempId);
            Map existData = this.iJdbcDao.queryForMapBySqlWithParams(sqlCheckCarExist, car.getVin(), car.getCompanyId());
            //根据vin判断目标数据是否已存在
            if(StringUtils.isEmpty(car.getVin())){
                continue;
            }
            if(existData.size() == 0){
                car.setId(null);
                this.iHibernateDao.saveOrUpdateEntity(car);
            }
            //满1000时，删除数据
            if (idList.size() == 1000) {
                this.impJdbcDao.batchDel("t_scm_car_ex", "id", idList);
                idList.clear();
            }
        }
        //删除遗留数据
        if (idList.size() > 0) {
            this.impJdbcDao.batchDel("t_scm_car_ex", "id", idList);
            idList.clear();
        }
    }

    /**
     * 导入库存数据
     */
    private static final String sqlCheckStockExist = "select id, vin from t_scm_stock_ex where vin = ? and company_id = ? limit 1";
    private void  impStock(String cid) throws  Exception{

        this.itScmInitStockDao.executeSQL("delete from t_scm_stock_ex where company_id=?",cid);
        //编写查询源数据 HQL
        String hql = "from TScmStockEx where companyId = :companyId";
        //设置参数
        Map<String, Object> params = new HashMap();
        params.put("companyId", cid);
        //查询公司下的全部车辆
        List<TScmStockEx> tScmStockEx = iImpHibernateDao.findEntityListByHqlWithParams(hql, params);
        if (tScmStockEx == null) {
            throw new Exception("无效的数据！");
        }
        //根据vin判断目标数据是否已存在
        List<Object> idList = new ArrayList();
        String tempId;
        for (TScmStockEx stock : tScmStockEx) {
            //源数据主键
            tempId = stock.getId();
            //源数据主键
            idList.add(tempId);
            Map existData = this.iJdbcDao.queryForMapBySqlWithParams(sqlCheckStockExist, stock.getVin(), stock.getCompanyId());
            if(StringUtils.isEmpty(stock.getVin())){
                continue;
            }
            if(existData.size() == 0){
                stock.setId(null);
                this.iHibernateDao.saveOrUpdateEntity(stock);
            }
            //满1000时，删除数据
            if (idList.size() == 1000) {
                this.impJdbcDao.batchDel("t_scm_stock_ex", "id", idList);
                idList.clear();
            }
        }
        //删除遗留数据
        if (idList.size() > 0) {
            this.impJdbcDao.batchDel("t_scm_stock_ex", "id", idList);
            idList.clear();
        }
    }

    /**
     * 导入入库数据
     */
    private static final String sqlCheckEnterExist = "select id, vin from t_scm_enter_ex where vin = ? and company_id = ? limit 1";
    private void impEnter(String cid) throws Exception{

        this.itScmInitStockDao.executeSQL("delete from t_scm_enter_ex where company_id=?",cid);
        //编写查询源数据 HQL
        String hql = "from TScmEnterEx where companyId = :companyId";
        //设置参数
        Map<String, Object> params = new HashMap();
        params.put("companyId", cid);
        //查询公司下的全部车辆
        List<TScmEnterEx> tScmEnterEx = iImpHibernateDao.findEntityListByHqlWithParams(hql, params);
        if (tScmEnterEx == null) {
            throw new Exception("无效的数据！");
        }
        //根据vin判断目标数据是否已存在
        List<Object> idList = new ArrayList();
        String tempId;
        for (TScmEnterEx enter : tScmEnterEx) {
            //源数据主键
            tempId = enter.getId();
            //源数据主键
            idList.add(tempId);
            //Map existData = this.iJdbcDao.queryForMapBySqlWithParams(sqlCheckEnterExist, enter.getVin(), enter.getCompanyId());
            if(StringUtils.isEmpty(enter.getVin())){
                continue;
            }
            //if(existData.size() == 0){
            enter.setId(null);
            this.iHibernateDao.saveOrUpdateEntity(enter);
            //}
            //满1000时，删除数据
            if (idList.size() == 1000) {
                this.impJdbcDao.batchDel("t_scm_enter_ex", "id", idList);
                idList.clear();
            }
        }
        //删除遗留数据
        if (idList.size() > 0) {
            this.impJdbcDao.batchDel("t_scm_enter_ex", "id", idList);
            idList.clear();
        }
    }

    /**
     * 查询没有进库的车辆
     * @param cid
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map ifmatch(String cid,String start,String limit) throws Exception{

        Common common = new Common();
        Boolean hasWhere = false;

        StringBuffer sqls = new StringBuffer();
         List<String> params = new ArrayList<String>();
         sqls.append("select * from t_scm_stock_ex e  where not EXISTS " +
                 "(select vin from t_scm_stock s where RIGHT(trim(s.vin), 8) = RIGHT(trim(e.vin), 8) and s.company_id=? ) and e.company_id=?");
         if (!StringUtils.isEmpty(cid)) {
             params.add(cid);
             params.add(cid);
        }

        sqls.append(" order by e.create_time desc");
        return this.itScmInitStockDao.page(sqls.toString(),params.toArray(),start,limit);
    }

    /**
     * 刷新车系车型配置
     * @param uid
     * @throws Exception
     */
    @Override
    public void refreshCar(String uid) throws Exception{

        String cid = TokenUtil.getCompanyId(uid);
        String sql = "select func_refreshCar(?) ";
        List<String> params = new ArrayList<String>();
        if(!StringUtils.isEmpty(cid)){
            params.add(cid);
        }
        this.itScmInitStockDao.list(sql,params.toArray());
    }


    /**
     * 插入到74的各个表中
     * @param data
     * @param uid
     * @throws Exception
     */
    @Override
    public void insertTo74(String data,String uid,String table) throws Exception{

        //获取数据
        Map map = TScmMapToJSON.json2Map(data);
        List<Map<String,Object>> list = (ArrayList) map.get("dataList");
        //插入数据
        this.insertData(list,uid,table);
    }

    /**
     * 链接74
     * @param uid
     * @param data
     * @throws Exception
     */
    @Override
    public void connect74(String uid,Map data,String table) throws Exception{

        String json = TScmMapToJSON.obj2JsonString(data);
        String address = "http://10.26.24.74:8080/CarService-0.0.1/scm/initStock/insertTo74?uid="+uid+"&token=0&table="+table;
        //String address = "http://192.168.1.16:8013/scm/initStock/insertTo74?uid="+uid+"&token=0&table="+table;

        PrintWriter out = null;
        BufferedReader in = null;
        String result = "";
        try {
            URL realUrl = new URL(address);
            // 打开和URL之间的连接
            URLConnection conn = realUrl.openConnection();
            // 设置通用的请求属性
            conn.setRequestProperty("accept", "*/*");
            conn.setRequestProperty("connection", "Keep-Alive");
            conn.setRequestProperty("user-agent",
                    "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;SV1)");
            // 发送POST请求必须设置如下两行
            conn.setDoOutput(true);
            conn.setDoInput(true);
            // 获取URLConnection对象对应的输出流
            out = new PrintWriter(conn.getOutputStream());
            // 发送请求参数
            out.print(json);
            // flush输出流的缓冲
            out.flush();
            // 定义BufferedReader输入流来读取URL的响应
            in = new BufferedReader(
                    new InputStreamReader(conn.getInputStream()));
            String line;
            while ((line = in.readLine()) != null) {
                result += line;
            }
            System.out.println(result);

        } catch (Exception e) {
            System.out.println("发送 POST 请求出现异常！"+e);
            e.printStackTrace();
        }
        //使用finally块来关闭输出流、输入流
        finally{
            try{
                if(out!=null){
                    out.close();
                }
                if(in!=null){
                    in.close();
                }
            }
            catch(IOException ex){
                ex.printStackTrace();
            }
        }
    }

    /**
     * 获取72数据再访问74
     * @param uid
     * @throws Exception
     */
    @Override
    public void getFrom72(String uid,String table) throws Exception{

        String cid = TokenUtil.getCompanyId(uid);
        //获取72数据
        List<Map<String,Object>> list = this.getData(table,cid);
        //链接74
        Map data = new HashMap();
        data.put("dataList",list);
        this.connect74(uid,data,table);
    }

    /**
     * 获取72数据
     * @param table
     * @param cid
     * @return
     */
    private List<Map<String,Object>> getData(String table,String cid) throws Exception{
        List<Map<String,Object>> list = new ArrayList<>();
        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        if("car".equals(table)){//车辆表
            sql.append("select * from t_scm_car where company_id = ?");
            params.add(cid);
        }
        if("stock".equals(table)){//库存表
            sql.append("select * from t_scm_stock where company_id = ?");
            params.add(cid);
        }
        if("enter".equals(table)){//入库表
            sql.append("select * from t_scm_enter_stock_record where company_id = ?");
            params.add(cid);
        }
        if("fa".equals(table)){//发票表
            sql.append("select * from t_scm_input_invoice where company_id = ?");
            params.add(cid);
        }
        if("ming".equals(table)){//发票明细表
            sql.append("select * from t_scm_input_invoice_detail where invoice_id in (" +
                    " select id from t_scm_input_invoice where company_id = ?)");
            params.add(cid);
        }
        if("price".equals(table)){//价格表
            sql.append("select * from t_scm_invoice_price where car_id in (" +
                    " select id from t_scm_car where company_id = ?)");
            params.add(cid);
        }
        list = this.itScmInitStockDao.list(sql.toString(),params.toArray());
        return list;
    }

    /**
     * 74上插入数据
     * @param list
     * @param uid
     * @throws Exception
     */
    private void insertData(List<Map<String,Object>> list,String uid,String table) throws Exception{

        switch (table){
            case "car"://车辆表
                this.insertCar(list);
                break;
            case "stock"://库存表
                this.insertStock(list);
                break;
            case "enter"://入库表
                this.insertEnter(list);
                break;
            case "fa"://发票表
                this.insertFa(list);
                break;
            case "ming"://发票明细表
                this.insertMing(list);
                break;
            case "price"://价格表
                this.insertPrice(list);
                break;
            default:
            break;
        }

    }

    private void insertCar(List<Map<String,Object>> list) throws Exception{
        for(Map<String,Object> map:list){
            List params = new ArrayList();
            String sql = "insert into t_scm_car values()";
            this.itScmInitStockDao.executeSQL(sql,params.toArray());
        }
    }
    private void insertStock(List<Map<String,Object>> list) throws Exception{
        for(Map<String,Object> map:list){
            List params = new ArrayList();
            String sql = "insert into t_scm_stock values()";
            this.itScmInitStockDao.executeSQL(sql,params.toArray());
        }
    }
    private void insertEnter(List<Map<String,Object>> list) throws Exception{
        for(Map<String,Object> map:list){
            List params = new ArrayList();
            String sql = "insert into t_scm_enter_stock_record values()";
            this.itScmInitStockDao.executeSQL(sql,params.toArray());
        }
    }
    private void insertFa(List<Map<String,Object>> list) throws Exception{
        for(Map<String,Object> map:list){
            List params = new ArrayList();
            String sql = "insert into t_scm_input_invoice values()";
            this.itScmInitStockDao.executeSQL(sql,params.toArray());
        }
    }
    private void insertMing(List<Map<String,Object>> list) throws Exception{
        for(Map<String,Object> map:list){
            List params = new ArrayList();
            String sql = "insert into t_scm_input_invoice_detail values()";
            this.itScmInitStockDao.executeSQL(sql,params.toArray());
        }
    }
    private void insertPrice(List<Map<String,Object>> list) throws Exception{
        for(Map<String,Object> map:list){
            List params = new ArrayList();
            String sql = "insert into t_scm_invoice_price values()";
            this.itScmInitStockDao.executeSQL(sql,params.toArray());
        }
    }

}
